﻿/*
author:田伟后 闵亚楠 薛若梅 钱波
last modify: 钱波
*/
var util = require('util');
var mysql = require('mysql');
var async = require('async');
var fs = require('fs');
var dbconfig = {
    host: '118.190.203.202',
    user: 'root',
    password: 'root',
    database: 'oil_depot',
    port: '3306',
    timezone: '+08:00',
    multipleStatements: true
};

var pool = mysql.createPool(dbconfig);
//var pool = null;
//公用函数区域
function is_not_empty(key, value) {
    if (key != null && key != '' && value != null)
        return true;
    return false;
}
//数组则返回数组的长度，否则返回0
function value_is_array(value) {
    if (Array.prototype.isPrototypeOf(value) == true) { //如果是数组
        return value.length;
    }
    return 0;
}
/*
创建数据表和视图
sql相关,创建表,创建表的sql语句
*/
function sql_create(tablename, fields, index) {
    var ret = "create table if not exists ";
    ret += tablename;
    ret += "(";
    for (var key in fields) {
        ret += " ";
        ret += key;
        ret += " ";
        var t = fields[key].type;
        if (t == undefined) {
            console.log("key's type not defined");
            t = "TEXT";
        }
        ret += t;
        ret += ",";
    }
    //直接替换
    if (index != null) {
        // for (var i = 0; i < index.length; i++) { // a,b
        //     var dex = util.format("UNIQUE  INDEX  %s_%s ( %s  ASC)", tablename, index[i], index[i]);
        for (var i = 0; i < index.length; i++) {//a,b  UNIQUE INDEX `a,b` (`name`, `status`) USING BTREE
            if (index[i].indexOf(',') > -1) {
                var arr = index[i].split(',');
                var str = index[i].replace(',', '_');
                var dex = util.format("UNIQUE  INDEX  %s_%s", tablename, str);
                dex += " ( ";
                for (let k = 0; k < arr.length; k++) {
                    if (k != 0) {
                        dex += ", ";
                    }
                    dex += arr[k];
                }
                dex += " ASC)";
            } else {
                var dex = util.format("UNIQUE  INDEX  %s_%s ( %s  ASC)", tablename, index[i], index[i]);
            }
            ret += dex;
            ret += ","
        }
    }
    ret = ret.substr(0, ret.length - 1);
    ret += ")";
    return ret;
}
function handle_table_leftjoin(table) {
    var sql = " select "
    var lfields = table.left.fields;
    var ltablename = table.left.name;
    var lalias = table.left.alias;
    var rtablename = table.right.name;
    var ralias = table.right.alias;
    sql += lfields;
    sql += ",";
    var rfields = table.right.fields;
    sql += rfields;
    sql += " from ";
    sql += ltablename;
    sql += " ";
    sql += lalias;
    sql += " left join ";
    sql += rtablename;
    sql += " ";
    sql += ralias;
    return sql;
}
function field_analyse(f, alias) {
    var str = "";
    str += f;
    var fieldx = str.split(',');
    var slen = fieldx.length;
    //console.log("slen:", slen);
    var out = "";
    var si = 0;
    if (slen == 1 && str == "*") {
        out = alias + ".*";
    }
    else {
        fieldx.map(function (item) {
            var text = item.trim();
            out += alias + "." + text + " ";// + text;
            si++;
            if (si < slen)
                out += ","
        });
    }

    return out;
}
function handle_table_leftjoin2(table) {
    var sql = " select "

    var lfields = field_analyse(table.left.fields, "a");
    var ltablename = table.left.name;
    var rtablename = table.right.name;
    sql += lfields;
    sql += ",";
    var rfields = field_analyse(table.right.fields, "b");
    sql += rfields;
    sql += " from ";
    sql += ltablename;
    sql += " a";
    sql += " left join ";
    sql += rtablename;
    sql += " b";
    return sql;
}



function update_sql(tablename, data) {
    var dataArray = Object.keys(data);
    var i = 0;
    var ret = "UPDATE ";
    ret += tablename;
    ret += " SET ";
    for (var key in data) {
        i++;
        var strtype = "";
        ret += key;
        ret += "=";
        ret += mysql.escape(data[key]);
        if (i != dataArray.length) {
            ret += ",";
        }
    }
    return ret;
}
function insert_replace_sql(tablename, data) {
    var sql = "";//"INSERT INTO  ";
    sql += tablename;
    sql += "(";
    var values = "VALUES (";
    for (var key in data) {
        sql += key;
        sql += ",";
        values += pool.escape(data[key])
        values += ","
    }
    sql = sql.substr(0, sql.length - 1);
    values = values.substr(0, values.length - 1);
    sql += ")";
    values += ")";
    sql += values;
    return sql;
}
function query(sql, options, callback) {
    if (pool == null) {
        // if(callback)
        callback(-1, "please init db first!");
        //console.log("please init db first!");
    }
    pool.getConnection(function (err, conn) {
        if (err) {
            console.log("err sql:", sql);
            //if(callback)
            callback(err, null, null);
        } else {
            conn.query(sql, options, function (err, results, fields) {
                conn.release();//释放连接  
                //  if(callback)
                callback(err, results, fields);
            });
        }
    });
};
function execute_sql(type, sql, callback) {
    query(sql, { timeout: 60000 }, function (err, results, fileld) {
         console.log(sql);
        if (callback == null) {
            return;
        }
        if (err) {
            console.log("err sql:" + this.querySql);
            callback(-1, err);
            return;
        }
        switch (type) {
            case 1://count
                callback(1, { count: results[0]['COUNT'] })
                break;
            case 2: //result
                if (results.length == 0)
                    callback(1, null);
                else {
                    callback(1, results);
                }
                break;
            case 3: //insert 
                if (results.affectedRows == 0)
                    callback(-1, "数据重复");
                else
                    callback(1, { id: results.insertId });
                break;
            case 4: //update ,replace ,delete
                callback(1, { id: results.affectedRows });
                break;
            case 5://row
                callback(1, results[0]);
                break;
        }

    })
}
//插入 正确返回（1，插入的id） 错误（-1，err）   
//如果插入key重复的话 返回 插入的id为 0；
function func_insert(tablename, data, callback) {
    var resql = insert_replace_sql(tablename, data);
    var sql = "INSERT INTO  ";
    sql += resql;
    execute_sql(3, sql, callback);
}
function func_replace(tablename, data, callback) {
    var resql = insert_replace_sql(tablename, data);
    var sql = "REPLACE INTO  ";
    sql += resql;
    execute_sql(4, sql, callback);
}
//data应该是一个数组
function func_insert_batch(tablename, datas, callback, sqlreturn) {
    if (Array.prototype.isPrototypeOf(datas) == false) { //如果不是数组
        return func_insert(tablename, datas, callback);
    }
    var sql = "INSERT IGNORE INTO " + tablename + "(";
    var values = " VALUES ";
    for (var i = 0; i < datas.length; i++) {
        var data = datas[i];
        var dataLength = Object.getOwnPropertyNames(data).length;
        values += "(";
        var j = 0;
        for (var key in data) {
            if (i === 0) {
                sql += key;
                sql += ",";
            }

            values += pool.escape(data[key])
            if (dataLength - 1 !== j) {
                values += ","
            }
            j++;
        }
        values += "),";
    }
    sql = sql.substr(0, sql.length - 1);
    values = values.substr(0, values.length - 1);
    sql += ")";
    sql += values;
    if (sqlreturn != null && sqlreturn == true)
        return sql;
    else
        execute_sql(4, sql, callback);
}
//替换 (insert的一种)  如果关键字或者索引 有相同的，就会把原纪录替换，没有就插入一条新数据。
function func_update(tablename, data, where, callback) {

    if (callback == null) {
        callback(-1, "callback is null");
        return;
    }
    if (where == null) {
        callback(-1, "where is null");
        return;
    }
    if (data == null) {
        callback(-1, "data is null, please do not kidding me");
        return;
    }
    var sql = update_sql(tablename, data);
    sql += where;
    execute_sql(4, sql, callback);
}
//删除记录
function func_delete(tablename, where, callback) {
    if (where == "") {
        console.log("where is null, please do not kidding me");
        return;
    }
    if (!tablename) {
        console.log("table name is null, please do not kidding me");
        return;
    }

    var sql = "DELETE FROM " + tablename;
    sql += where;
    //if (this.trans) {
    //    this.sqlparamsEntities.push({ sql: sql });
    //    return;
    //}
    execute_sql(4, sql, callback);
}

//事务处理
function func_execTrans(sqls, callback) {
    pool.getConnection(function (err1, connection) {
        if (err1) {
            return callback(-1, err1);
        }
        connection.beginTransaction(function (err2) {
            if (err2) {
                return callback(-1, err2);
            }
            if (sqls.length === 0) {
                console.log("sqls is null ");
                return callback(-1, null);
            }
            var tasks = [];
            sqls.forEach(function (sql) {
                var task = function (cb) {
                    //console.log("now the sql is :", sql);
                    connection.query(sql, null, function (err3, result, fields) {
                        cb(err3, result);
                    })
                };
                tasks.push(task);
            });
            //commit 是最后一个函数 
            async.series(tasks, function (err4, result) {
                if (!err4) {
                    connection.commit(function (err5) {
                        if (err5) {
                            connection.rollback(function () {
                                connection.release();
                            });
                            callback(-1, "commit error rollback");
                        } else {
                            connection.release();
                            callback(1, "success");
                        }
                    });
                }
                else {
                    connection.rollback(function () {
                        connection.release();
                    })
                    callback(-1, "rollback");
                }

            })

        });
    });
}


// 创建表创建视图
function db_create(define, cb) {
    var table_field = null;
    var table_view = null;
    if (define == null) {
        console.log("no define ,error!");
        return;
    }
    else {
        table_field = define.table_field;
        table_view = define.table_view;
    }
    if (table_field != null) {
        for (var i = 0; i < table_field.length; i++) {
            let name = table_field[i].tablename;
            let fields = table_field[i].fields;
            let index = table_field[i].index;
            let str_create = sql_create(name, fields, index);
            query(str_create, null, cb);
        }
    }
    if (table_view != null) {
        for (var i = 0; i < table_view.length; i++) {
            if (table_view[i].type == "left") {
                var sql = "";
                if (table_view[i].table.left.alias != undefined) {
                    sql = handle_table_leftjoin(table_view[i].table)
                }
                else {
                    sql = handle_table_leftjoin2(table_view[i].table);
                }

                sql += " ";
                sql += table_view[i].where;
                let viewname = table_view[i].tablename;
                let view = "CREATE OR REPLACE VIEW " + viewname + " as ";

                sql = view + sql;
                //console.log("ccccc:",sql);
                query(sql, null, cb);
            }
            else if (table_view[i].type == "group") {
                let viewname = table_view[i].tablename;
                var sql = "CREATE OR REPLACE VIEW " + table_view[i].tablename;
                sql += " as ";
                sql += table_view[i].sql;
                //console.log(sql);
                query(sql, null, cb);
            }
        }
    }
}
//创建
//map_create(define);




function sql_func() {
    this.fieldS = "*",
        this.whereS = "",
        this.orerbyS = "",
        this.limitS = "",
        this.querySql = "",
        this.countSql = "",
        this.groupbyS = "",
        this.havingS = "",
        this.distinctS = false;
    this.clear = function () {
        this.fieldS = "*";
        this.whereS = "";
        this.orerbyS = "";
        this.limitS = "";
        this.querySql = "";
        this.countSql = "";
        this.groupbyS = "";
        this.havingS = "";
        this.distinctS = false;
    }
    this.where_in = function (field, dataArray) {
        var i, wx = "";
        if (this.whereS == "") {
            this.whereS = " WHERE " + field + " IN (";
        } else {
            wx = ' AND ' + field + ' IN (';
        }
        for (i = 0; i < dataArray.length; i++) {
            wx += pool.escape(dataArray[i]);
            if (dataArray.length != i + 1) {
                wx += " , ";
            } else {

                wx += " ) ";
            }
        }
        this.whereS += wx;
    };

    function split_str_array(str_array) {
        //判决是否是数组query =[a,b,c],where in
        var ret = " in( ";
        let vs = JSON.parse(str_array);
        for (let i = 0; i < vs.length; i++) {
            ret += pool.escape(vs[i]);
            if (i != vs.length - 1)
                ret += ",";
        }
        ret += ")"
        return ret;
    }

    //  自定义字符串:  $where = "name='Joe' AND status='boss' OR status='active'"; db.where($where);
    //  对象的形式 {id:2,num:">3",num2:"=8",num3:"2,3",num4:[1,2,3]-->where in }  属性之间
    this.where = function (whereData) {

        if (this.whereS == "") {
            this.whereS = " WHERE 1=1 ";
        }
        if (typeof (whereData) == "string") {
            //" and field is not null"
            this.whereS += whereData;
            return;
        }
        if (typeof (whereData) != 'object') {
            console.log("where func wheredata not and object!");
            return;
        }

        var fh = [">=", "<=", "!=", "=", ">", "<"];

        for (var key in whereData) {
            var value = whereData[key] + '';
            //
            value = value.trim();
            if (value == '' || value == ',') {
                continue;
            }
            var wx = '';
            var real_data;
            var cp = "";
            var find_fh = 0;
            for (var i = 0; i < fh.length; i++) {
                var len = fh[i].length;
                cp = value.substr(0, len);
                if (fh[i] === cp) {
                    real_data = value.substr(len, value.length - len);
                    real_data = real_data.trim();
                    find_fh = 1;
                    break;
                }
            }

            wx += " AND ";
            wx += key;
            if (find_fh == 0) {
                if (value[0] == '[' && value[value.length - 1] == ']') {
                    let vsa = split_str_array(value);
                    wx += vsa;
                }
                else {
                    var num = value.indexOf(",");
                    if (num !== -1) {
                        var f = value.substr(0, num);
                        f = f.trim();
                        var t = value.substr(num + 1, value.length - num);
                        t = t.trim();
                        if (f == "" && t != "")
                            wx += " <= " + pool.escape(t);
                        else if (f != "" && t == "")
                            wx += " >= " + pool.escape(f);
                        else if (f != "" && t != "")
                            wx += " between '" + f + "' and '" + t + "'";
                    }
                    else {
                        wx += " like ";
                        var likes = "%" + value + "%"
                        wx += pool.escape(likes);
                    }
                }
            }
            else {
                if (real_data == '') {
                    continue;
                }
                wx += cp;
                wx += pool.escape(real_data);
            }
            this.whereS += wx;
        }
    };
    this.or_where = function (whereData) {
        if (this.whereS == "") {
            this.whereS = " WHERE 1!=1 ";
        }
        for (var key in whereData) {
            var wx = " OR " + key;
            if (key.indexOf("!=") !== -1 || key.indexOf(">") !== -1 || key.indexOf("<") !== -1 || key.indexOf("=") !== -1) {
                wx += " ";
            } else {
                wx += " = ";
            }
            wx += pool.escape(whereData[key]);
            this.whereS += wx;
        }
    };
    this.select = function (fields) {
        //钱波修改成为三目
        if (fields != null) {
            fields = fields.trim();
            this.fieldS = fields == "" ? "*" : fields;
            return;
        }
        this.fieldS = "*";
    };
    this.distinct = function () {
        this.distinctS = true;
    };
    this.limit = function (offset, rows) {
        this.limitS = " LIMIT " + offset + "," + rows;
    };
    this.like = function (whereData) {
        if (this.whereS == "")
            this.whereS = " WHERE 1=1 ";
        for (var key in whereData) {
            this.whereS += " AND " + key + " LIKE " + pool.escape("%" + whereData[key] + "%");
        }
    };
    this.or_like = function (whereData) {
        if (this.whereS == "") {
            this.whereS = " WHERE 1!=1 ";
        }
        for (var key in whereData) {
            var wx = '' + " OR " + key + " LIKE " + pool.escape("%" + whereData[key] + "%");
            this.whereS += wx;
        }

    },
        this.order_by = function (field, sort) {
            this.orerbyS = " ORDER BY " + field + " " + sort + " ";
        },
        this.group_by = function (field) {
            this.groupbyS = " GROUP BY " + field + " ";
        },
        this.having = function (havingSql) {
            this.havingS = " HAVING  " + havingSql + " ";
        },
        //钱波修改 it is ok
        this.createsql = function (tablename, ispage) {
            if (tablename == "" || tablename == null) {
                console.log("table name is null ");
                return null;
            }
            var sql = "";
            var coSql = "";

            if (this.distinctS) {
                coSql = "SELECT  COUNT(DISTINCT " + this.fieldS + ") COUNT FROM  " + tablename;
                sql = "SELECT  DISTINCT " + this.fieldS + " FROM " + tablename + " ";
            } else {
                coSql = "SELECT  COUNT(*) COUNT FROM  " + tablename;
                sql = "SELECT  " + this.fieldS + " FROM " + tablename + " ";
            }
            var queryCon = "";
            var queryCountSql = "";
            if (this.whereS != "") {
                queryCon += this.whereS;
                queryCountSql += this.whereS;
            }
            if (this.groupbyS != "") {
                queryCon += this.groupbyS;
                queryCountSql += this.groupbyS;
            }

            if (this.havingS != "") {
                queryCon += this.havingS;
                queryCountSql += this.havingS;
            }
            if (this.orerbyS != "") {
                queryCon += this.orerbyS;
            }
            if (this.limitS != "") {
                queryCon += this.limitS;
            }
            this.querySql = sql + queryCon;
            this.countSql = coSql + queryCountSql;
            if (ispage == true) { //如果获取的是页面数据，返回两个sql相加
                var sqls = this.countSql + ";" + this.querySql;
                console.log("createsql:", sqls);
                return sqls;
            }
            return this.querySql;
        };
}

//钱波修改
//在insert，querys 自己写sql语句时不需要产生s对象，则不创建 sql_func对象
function sqldb() {
    this.s = null;// new sql_func();

    this.init = function (dbconfig, define, cb) {
        if (pool == null) {
            pool = mysql.createPool(dbconfig);
            db_create(define, cb);
        }
    }
    this.count = function (callback) {
        execute_sql(1, this.s.countSql, callback);
    };
    //返回数组    
    this.result = function (callback) {
        execute_sql(2, this.s.querySql, callback);
    };
    this.page_count_result = function (callback) {
        var sqls = this.s.countSql + ";" + this.s.querySql;
        //console.log("page_count_result:", sqls);
        execute_sql(2, sqls, callback);
    }

    //返回对象，如果是多行，就返回第一行    
    this.row = function (callback) {
        execute_sql(5, this.s.querySql, callback);
    };
    this.delete = function (tablename, callback) {
        func_delete(tablename, this.s.whereS, callback);
    };
    this.update = function (tablename, data, callback) {
        func_update(tablename, data, this.s.whereS, callback);
    };
    this.insert = function (tablename, data, callback) {
        func_insert_batch(tablename, data, callback);
    };
    //[{table:"x",arr:[{id:1,name:"ddd"},{id:2,name:"eee"}]},
    // {table:"y",arr:[{id:2,address:"xxx"},{},{}]},
    //]
    //插入多个不同表的多个字段
    this.insert_plus = function (datas, callback) {
        var sqlall = [];
        for (var i = 0; i < datas.length; i++) {
            let sql = func_insert_batch(datas[i].table, datas[i].arr, null, true);
            sqlall.push(sql);
        }
        console.log('insert sql:', sqlall);
        func_execTrans(sqlall, callback);
    };
    this.update = function (tablename, data, callback) {
        func_update(tablename, data, this.s.whereS, callback);
    };
    this.querys = function (sql, callback) {
        execute_sql(2, sql, callback);
    }
    //obj: tablename
    this.querysql = function (clear, obj) {
        if (obj == null || obj.tablename == null) {
            console.log(-1, "some null object");
            return null;
        }
        else
            // console.log("obj is ", obj);
            if (this.s == null) this.s = new sql_func();
        if (clear)
            this.s.clear();
        if (obj.field != null) {
            this.s.select(obj.field);
        }
        else if (obj.fields != null)
            this.s.select(obj.fields);
        if (obj.where_in != null) {
            if (is_not_empty(obj.where_in.field, obj.where_in.arr)) {
                if (value_is_array(obj.where_in.arr) > 0)
                    this.s.where_in(obj.where_in.field, obj.where_in.arr);
                //else
                //    s.where({ obj.f_in: "=" + obj.f_in_array });
            }
        }
        if (obj.query != null && Object.keys(obj.query).length > 0) {
            this.s.where(obj.query);
        }
        if (obj.page != null) { //分页参数放在了page之中
            //为了容错
            let page = obj.page;
            if (page.currentPage != null && page.itemsPerPage != null) {
                var start = (page.currentPage - 1) * page.itemsPerPage;
                this.s.limit(start, page.itemsPerPage);
            }
            if (page.limit != null) //不是limit就是rows
                page.rows = page.limit;
            if (page.page != null && page.rows != null) {
                var start = (page.page - 1) * page.rows;
                this.s.limit(start, page.rows);
            }
            if (page.sidx != null && page.sord != null) {
                this.s.order_by(page.sidx, page.sord);
            }
        }
        //

        if (obj.extra_where != null)
            this.s.where(obj.extra_where);



        if (obj.distinct)
            this.s.distinct();
        return this.s.createsql(obj.tablename);
    }
}

module.exports.sqldb = sqldb;

//module.exports.db_create = db_create;


//update 表 SET 字段 =  CASE Id WHEN 1 THEN 123 END WHERE ID  = 1;
//测试事务处理 ok
//var sqls = [];
//var sql1 = "delete from project where id = 343";
//var sql2 = "insert into project(name1) values('11111')";
//var sql3 = "insert into project(name) values('22222')";
//sqls.push(sql1);
//sqls.push(sql2);
//sqls.push(sql3);
//var datas=[
//    {name:"123",des:"ddd我的描述",address:"dddd"},
//    {name:"123",des:"ddd我的描述",address:"dddd"},
//    {name:"123",des:"ddd我的描述",address:"dddd"},
//    {name:"123",des:"ddd我的描述",address:"dddd"},
//    {name:"123",des:"ddd我的描述",address:"dddd"},
//    {name:"123",des:"ddd我的描述",address:"dddd"},
//    {name:"1234",des:"ddd",address:"dddd"},
//    {name:"1235",des:"ddd",address:"dddd"},
//    {name:"1236",des:"ddd",address:"dddd"},
//    {name:"1237",des:"ddd",address:"dddd"},
//    {name:"1238",des:"ddd",address:"dddd"},
//    {name:"1239",des:"ddd",address:"dddd"},
//    {name:"1230",des:"ddd",address:"dddd"},
//    {name:"12311",des:"ddd",address:"dddd"},
//    {name:"12313",des:"ddd",address:"dddd"},
//    {name:"12314",des:"ddd",address:"dddd"},
//    {name:"12315",des:"ddd",address:"dddd"},
//    {name:"123121",des:"ddd",address:"dddd"},
//    {name:"123122",des:"ddd",address:"dddd"},
//    {name:"123123",des:"ddd",address:"dddd"},
//    {name:"123124",des:"ddd",address:"dddd"},
//    {name:"123125",des:"ddd",address:"dddd"},
//    {name:"123126",des:"ddd",address:"dddd"},
//    {name:"123127",des:"ddd",address:"dddd"},
//    {name:"123128",des:"ddd",address:"ddddadadfewweqowqeioioewq物品睡觉哦"},
//];
//function callback_t(result,ret){
//    console.log(result,ret);
//}
//func_insert_batch("project", datas, callback_t);
//function trans_callback(ret, info){
//    console.log("trans test:",ret, info);
//}
//func_execTrans(sqls, trans_callback);

